/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Designation;

import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(DesignationBean db) throws SQLException {
        int i = 0, k=0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into designation values(designationid.nextval,?,sysdate)");
            pre.setString(1, db.getDesignation());
            k = pre.executeUpdate();
            if (k != 0) {
                i = getMaxId();
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(designation_id) from designation");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                i = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static DesignationBean viewSpecific(String cname, int val) throws SQLException {
        DesignationBean db = new DesignationBean();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from designation where " + cname + "=?");
            pre.setInt(1, val);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                db.setDesignationId(rs.getInt(1));
                db.setDesignation(rs.getString(2));
                db.setEntryDate(rs.getDate(3));
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return db;

    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();
        
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from designation order by designation_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                DesignationBean db = new DesignationBean();
                db.setDesignationId(rs.getInt(1));
                db.setDesignation(rs.getString(2));
                db.setEntryDate(rs.getDate(3));
                list.add(db);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;

    }
public static int edit(DesignationBean db) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update designation set designation=?, entrydate=sysdate where designation_id=?");
            pre.setString( 1, db.getDesignation());
            pre.setInt(2, db.getDesignationId());
            i = pre.executeUpdate();

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    public static int delete(int id) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("delete from designation where designation_id=?");
              pre.setInt(1,id);
              i=pre.executeUpdate();
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }      
}
